<?php
function get_kh_theo_ten($tenncc) {
  $query = db_select('dt_khach_hang', 'kh');
  $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'so_dien_thoai','hien_thi'));
  $query->condition('kh.ten_khach_hang', $tenncc, '=');
  $query->condition('kh.hien_thi', 0);
  $result = $query->execute();
  $data_khten = '';
  while ($rs = $result->fetchObject()) {
    $data_khten = $rs->ma_khach_hang;
  }
  return $data_khten;
}

  function get_kh_theo_sdt($sdt) {
    $query = db_select('dt_khach_hang', 'kh');
    $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'so_dien_thoai','hien_thi'));
    $query->condition('kh.so_dien_thoai', $sdt, '=');
    $query->condition('kh.hien_thi', 0);
    $result = $query->execute();
    $data_khsdt = '';
    while ($rs = $result->fetchObject()) {
      $data_khsdt = $rs->ma_khach_hang;
    }
    return $data_khsdt;
  }

  function get_khach_hang($header = array(), $filter) {
    $query = db_select('dt_khach_hang', 'kh')->extend('PagerDefault')->extend('TableSort');
    $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'so_dien_thoai', 'tien_no', 'dia_chi', 'thoi_gian', 'loai', 'ghi_chu'));
    if ($filter['ma_ref']) {
      $query->condition('kh.ma_khach_hang', $filter['ma_ref']);
    }
    $query->condition('kh.hien_thi', 0);
    //$query->orderBy('kh.ten_khach_hang','ASC');
    $query->orderByHeader($header);
    $result = $query->limit(40)->execute();
    $data = array();
    $admin_perm = user_access('admin xoa');
    $edit_perm = user_access('sua khach hang');
    while ($rs = $result->fetchObject()) {
      $output = '<a href="khach_hang/danh_sach_khach_hang/xoa_khach_hang/'.$rs->ma_khach_hang.'" onclick="return check_xoa();">Xóa</a>';
      $data[] = array(
        'ma_khach_hang' => $rs->ma_khach_hang,
        'ten_khach_hang' => $rs->ten_khach_hang,
        'tong_tien' => number_format($rs->tien_no),
        'so_dien_thoai' => $rs->so_dien_thoai,
        'dia_chi' => $rs->dia_chi,
        //'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'loai' => $rs->loai ? 'Đặc biệt' : 'Bình thường',
        'thao_tac' => ($admin_perm ? ($output.' | ')
          : '').($edit_perm ? l('Sửa', 'khach_hang/sua_khach_hang/' . $rs->ma_khach_hang)
          : '') . dt_view_ghi_chu('vt_' . $rs->id, 'Khách hàng : ' . $rs->ten_khach_hang, $rs->ghi_chu)
      );
    }
    return $data;
  }

  function get_tong_cong_no_kh() {
    $query = db_select('dt_khach_hang', 'kh');
    $query->addExpression('SUM(tien_no)', 'tong_tien');
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        array('data' => 'Tổng công nợ', 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($rs->tong_tien) . ' VNĐ', 'class' => array('text-bold')),
        // number_format($rs->tong_tien),
        // array('data' => '', 'colspan' => 5),
      );
    }
    return $data;
  }

  function insert_khachhang($uid, $ten_khach_hang, $dia_chi, $so_dien_thoai, $ghi_chu, $loai, $tien_no) {
    $thoi_gian = time();
    $month = date('n');
    $year = date('Y');
    $end_month = mktime(0, 0, 0, $month + 1, 1, $year) - 1;
    // $loai=0;
    $query = db_insert('dt_khach_hang');
    $arr = array(
      'uid' => $uid,
      'ma_khach_hang' => '',
      'ten_khach_hang' => $ten_khach_hang,
      'dia_chi' => $dia_chi,
      'so_dien_thoai' => $so_dien_thoai,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian,
      'loai' => $loai,
      'tien_no' => $tien_no,
      'du_km_dau_thang' => 0,
      'du_km' => 0,
      'thoi_gian_mua_cuoi' => $end_month
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_khach_hang = $id;
    while (strlen($ma_khach_hang) < 3) {
      $ma_khach_hang = '0' . $ma_khach_hang;
    }
    $ma_khach_hang = 'KH_' . $ma_khach_hang;
    db_update('dt_khach_hang')
      ->fields(array(
                    'ma_khach_hang' => $ma_khach_hang,
               ))
      ->condition('id', $id)
      ->execute();
    $query_n = db_insert('dt_khach_hang_du_lieu');
    $arr_n = array(
      'uid' => $uid,
      'ma_khach_hang' => $ma_khach_hang,
      'thoi_gian' => $thoi_gian,
      'tien_no' => $tien_no
    );
    $query_n->fields(array_keys($arr_n));
    $query_n->values($arr_n);
    $query_n->execute();
    return $ma_khach_hang;
  }

  function edit_khachhang($uid, $ma_khach_hang, $ten_khach_hang, $dia_chi, $so_dien_thoai, $ghi_chu, $loai) {
    db_update('dt_khach_hang')
      ->fields(array(
                    'ten_khach_hang' => $ten_khach_hang,
                    'dia_chi' => $dia_chi,
                    'so_dien_thoai' => $so_dien_thoai,
                    'loai' => $loai,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_khach_hang', $ma_khach_hang)
      ->execute();
  }
  function edit_khachhang_admin($uid, $ma_khach_hang, $ten_khach_hang, $dia_chi, $so_dien_thoai, $ghi_chu, $loai,$tien_no) {
    db_update('dt_khach_hang')
      ->fields(array(
                    'ten_khach_hang' => $ten_khach_hang,
                    'dia_chi' => $dia_chi,
                    'so_dien_thoai' => $so_dien_thoai,
                    'loai' => $loai,
                 'tien_no' => $tien_no,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_khach_hang', $ma_khach_hang)
      ->execute();
  }

  function get_khach_hang_autocomplete($string) {
    $query = db_select('dt_khach_hang', 'kh')->extend('PagerDefault');
    $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang'));
    $query->condition('kh.hien_thi',0);
    $query->condition(
      db_or()->
        condition('kh.ten_khach_hang', '%' . db_like($string) . '%', 'LIKE')->
      // condition('kh.ma_khach_hang', '%' . db_like($string) . '%', 'LIKE')->
        condition('kh.ma_khach_hang', $string, '=')->
        condition('kh.so_dien_thoai', $string, '=')->
        condition('kh.ma_khach_hang', 'KH_' . $string, '=')
    );
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']'] = $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']';
    }
    return $data;
  }

  function get_khach_hang_theo_ma($ma_khach_hang) {
    $query = db_select('dt_khach_hang', 'kh');
    $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'dia_chi', 'so_dien_thoai', 'loai', 'tien_no', 'ghi_chu', 'thoi_gian_mua_cuoi', 'du_km_dau_thang', 'du_km'));
    $query->condition('kh.ma_khach_hang', $ma_khach_hang, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function get_phieu_thu_san_pham($header, $ma_ncc) {
    $query = db_select('dt_phieu_thu_thanh_pham', 'ttp')->extend('PagerDefault')->extend('TableSort');
    $query->fields('ttp', array('id', 'ma_phieu_thu', 'ma_khach_hang', 'ma_thu_quy', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'nguoi_nop', 'thanh_toan', 'thoi_gian'));
    $query->condition('ttp.ma_khach_hang', $ma_ncc);
    $query->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = ttp.ma_khach_hang');
    $query->addField('kd', 'ten_khach_hang');
    $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = ttp.ma_thu_quy');
    $query->addField('tq', 'ten_thu_quy');
    $query->orderByHeader($header);
    $result = $query->orderBy('ttp.thoi_gian', 'DESC')->limit(15)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        'ma_phieu_thu' => $rs->ma_phieu_thu,
        'ma_khach_hang' => $rs->ten_khach_hang,
        'tong_tien_chua_giam' => number_format($rs->tong_tien_chua_giam),
        'tong_tien' => number_format($rs->tong_tien),
        'tien_bot' => number_format($rs->tien_bot),
        'ma_thu_quy' => $rs->ten_thu_quy,
        'thanh_toan' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền',
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/thusanpham/' . $rs->ma_phieu_thu)
      );
    }
    return $data;
  }

  function get_thong_ke_kh_thang($start_month, $end_month, $ma_ref) {
    $query = db_select('dt_khach_hang', 'kh');
    $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'thoi_gian'));
    $query->condition('kh.hien_thi', 0);
    $query->addExpression('(SELECT SUM(n0.tong_tien) FROM {dt_san_pham_nguyen} AS n0 WHERE kh.ma_khach_hang = n0.ma_khach_hang AND n0.thoi_gian < :n0_start_month)', 'nguyen_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(b0.tong_tien_chua_giam) FROM {dt_phieu_thu_thanh_pham} AS b0 WHERE kh.ma_khach_hang = b0.ma_khach_hang AND b0.thoi_gian < :b0_start_month)', 'thu_den_thang', array(':b0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(t0.thuc_thu) FROM {dt_ban_hang} AS t0 WHERE kh.ma_khach_hang = t0.ma_khach_hang AND t0.thoi_gian < :t0_start_month)', 'ban_den_thang', array(':t0_start_month' => $start_month));
    $query->addExpression('(SELECT d.tien_no FROM {dt_khach_hang_du_lieu} AS d WHERE kh.ma_khach_hang = d.ma_khach_hang AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));


    $query->addExpression('(SELECT SUM(n1.tong_tien) FROM {dt_san_pham_nguyen} AS n1 WHERE kh.ma_khach_hang = n1.ma_khach_hang AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nguyen_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(b1.tong_tien_chua_giam) FROM {dt_phieu_thu_thanh_pham} AS b1 WHERE kh.ma_khach_hang = b1.ma_khach_hang AND b1.thoi_gian >= :b1_start_month AND b1.thoi_gian <= :b1_end_month)', 'thu_thang', array(':b1_start_month' => $start_month, ':b1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(t1.thuc_thu) FROM {dt_ban_hang} AS t1 WHERE kh.ma_khach_hang = t1.ma_khach_hang AND t1.thoi_gian >= :t1_start_month AND t1.thoi_gian <= :t1_end_month)', 'ban_thang', array(':t1_start_month' => $start_month, ':t1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(c1.tong_tien) FROM {dt_phieu_chi_le} AS c1 WHERE kh.ma_khach_hang = c1.ma_khach_hang AND c1.thoi_gian >= :c1_start_month AND c1.thoi_gian <= :c1_end_month)', 'chi_thang', array(':c1_start_month' => $start_month, ':c1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(km1.so_luong * km1.tru_tien ) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS km1 ,{dt_ban_hang} AS t1 WHERE km1.ma_phieu_ban_hang = t1.ma_phieu_ban_hang and  kh.ma_khach_hang = t1.ma_khach_hang AND km1.thoi_gian >= :km1_start_month AND km1.thoi_gian <= :km1_end_month)', 'km_thang', array(':km1_start_month' => $start_month, ':km1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(h1.so_luong * h1.don_gia_tru_tien ) FROM {dt_san_pham_hoi_chi_tiet} AS h1 ,{dt_san_pham_hoi} AS t1 WHERE h1.ma_phieu_hang_hoi = t1.ma_phieu_hang_hoi and  kh.ma_khach_hang = t1.ma_khach_hang AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'hoi_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tb1.tong_tien - tb1.tong_tien_tru - tb1.thuc_thu) FROM {dt_ban_hang} AS tb1 WHERE kh.ma_khach_hang = tb1.ma_khach_hang AND tb1.thoi_gian >= :tb1_start_month AND tb1.thoi_gian <= :tb1_end_month)', 'tbot_thang', array(':tb1_start_month' => $start_month, ':tb1_end_month' => $end_month));

    $query->addExpression('(SELECT SUM(n2.tong_tien) FROM {dt_san_pham_nguyen} AS n2 WHERE kh.ma_khach_hang = n2.ma_khach_hang AND n2.thoi_gian < :n2_start_month)', 'nguyen_cuoi_thang', array(':n2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(b2.tong_tien_chua_giam) FROM {dt_phieu_thu_thanh_pham} AS b2 WHERE kh.ma_khach_hang = b2.ma_khach_hang AND b2.thoi_gian < :b2_start_month)', 'thu_cuoi_thang', array(':b2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(t2.thuc_thu) FROM {dt_ban_hang} AS t2 WHERE kh.ma_khach_hang = t2.ma_khach_hang AND t2.thoi_gian < :t2_start_month)', 'ban_cuoi_thang', array(':t2_start_month' => $end_month));
    if ($ma_ref) {
      $query->condition('kh.ma_khach_hang', $ma_ref);
    }
    $result = $query->orderBy('kh.ten_khach_hang')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        //l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/trong_thang/' . $rs->ma_vat_tu),
        array('data' => $rs->ten_khach_hang . '[' . $rs->ma_khach_hang . ']', 'class' => array('text-center')),
        number_format($rs->ban_den_thang - $rs->nguyen_den_thang - $rs->thu_den_thang + $rs->du),
        $rs->ban_thang
          ? l(number_format($rs->ban_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/ban/trong_thang/' . $rs->ma_khach_hang)
          : '',
        $rs->thu_thang
          ? l(number_format($rs->thu_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/thu/trong_thang/' . $rs->ma_khach_hang)
          : '',
        $rs->nguyen_thang
          ? l(number_format($rs->nguyen_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/nguyen/trong_thang/' . $rs->ma_khach_hang)
          : '',
        $rs->km_thang
          ? l(number_format($rs->km_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/km/trong_thang/' . $rs->ma_khach_hang)
          : '',
        $rs->hoi_thang
          ? l(number_format($rs->hoi_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/hoi/trong_thang/' . $rs->ma_khach_hang)
          : '',
        $rs->tbot_thang
          ? l(number_format($rs->tbot_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/bot/trong_thang/' . $rs->ma_khach_hang)
          : '',
        $rs->chi_thang
          ? l(number_format($rs->chi_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/chile/trong_thang/' . $rs->ma_khach_hang)
          : '',
        number_format($rs->ban_cuoi_thang - $rs->nguyen_cuoi_thang - $rs->thu_cuoi_thang + $rs->du),
      );
    }
    return $data;
  }

  function get_thong_ke_kh_ngay($start_date, $end_date, $start_month, $end_month) {
    $query = db_select('dt_khach_hang', 'kh');
    $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'thoi_gian'));
    $query->addExpression('(SELECT SUM(n0.tong_tien) FROM {dt_san_pham_nguyen} AS n0 WHERE kh.ma_khach_hang = n0.ma_khach_hang AND n0.thoi_gian < :n0_start_month)', 'nguyen_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(b0.tong_tien_chua_giam) FROM {dt_phieu_thu_thanh_pham} AS b0 WHERE kh.ma_khach_hang = b0.ma_khach_hang AND b0.thoi_gian < :b0_start_month)', 'thu_den_thang', array(':b0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(t0.thuc_thu) FROM {dt_ban_hang} AS t0 WHERE kh.ma_khach_hang = t0.ma_khach_hang AND t0.thoi_gian < :t0_start_month)', 'ban_den_thang', array(':t0_start_month' => $start_month));
    $query->addExpression('(SELECT d.tien_no FROM {dt_khach_hang_du_lieu} AS d WHERE kh.ma_khach_hang = d.ma_khach_hang AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n1.tong_tien) FROM {dt_san_pham_nguyen} AS n1 WHERE kh.ma_khach_hang = n1.ma_khach_hang AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nguyen_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(b1.tong_tien_chua_giam) FROM {dt_phieu_thu_thanh_pham} AS b1 WHERE kh.ma_khach_hang = b1.ma_khach_hang AND b1.thoi_gian >= :b1_start_month AND b1.thoi_gian <= :b1_end_month)', 'thu_thang', array(':b1_start_month' => $start_month, ':b1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(t1.thuc_thu) FROM {dt_ban_hang} AS t1 WHERE kh.ma_khach_hang = t1.ma_khach_hang AND t1.thoi_gian >= :t1_start_month AND t1.thoi_gian <= :t1_end_month)', 'ban_thang', array(':t1_start_month' => $start_month, ':t1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(c1.tong_tien) FROM {dt_phieu_chi_le} AS c1 WHERE kh.ma_khach_hang = c1.ma_khach_hang AND c1.thoi_gian >= :c1_start_month AND c1.thoi_gian <= :c1_end_month)', 'chi_thang', array(':c1_start_month' => $start_month, ':c1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(km1.so_luong * km1.tru_tien ) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS km1 ,{dt_ban_hang} AS t1 WHERE km1.ma_phieu_ban_hang = t1.ma_phieu_ban_hang and kh.ma_khach_hang = t1.ma_khach_hang AND km1.thoi_gian >= :km1_start_month AND km1.thoi_gian <= :km1_end_month)', 'km_thang', array(':km1_start_month' => $start_month, ':km1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(h1.so_luong * h1.don_gia_tru_tien ) FROM {dt_san_pham_hoi_chi_tiet} AS h1 ,{dt_san_pham_hoi} AS t1 WHERE h1.ma_phieu_hang_hoi = t1.ma_phieu_hang_hoi and kh.ma_khach_hang = t1.ma_khach_hang AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'hoi_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tb1.tong_tien - tb1.tong_tien_tru - tb1.thuc_thu) FROM {dt_ban_hang} AS tb1 WHERE kh.ma_khach_hang = tb1.ma_khach_hang AND tb1.thoi_gian >= :tb1_start_month AND tb1.thoi_gian <= :tb1_end_month)', 'tbot_thang', array(':tb1_start_month' => $start_month, ':tb1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.tong_tien) FROM {dt_san_pham_nguyen} AS n2 WHERE kh.ma_khach_hang = n2.ma_khach_hang AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'nguyen_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(b2.tong_tien_chua_giam) FROM {dt_phieu_thu_thanh_pham} AS b2 WHERE kh.ma_khach_hang = b2.ma_khach_hang AND b2.thoi_gian >= :b2_start_date AND b2.thoi_gian <= :b2_end_date)', 'thu_ngay', array(':b2_start_date' => $start_date, ':b2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(t2.thuc_thu) FROM {dt_ban_hang} AS t2 WHERE kh.ma_khach_hang = t2.ma_khach_hang AND t2.thoi_gian >= :t2_start_date AND t2.thoi_gian <= :t2_end_date)', 'ban_ngay', array(':t2_start_date' => $start_date, ':t2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(c2.tong_tien) FROM {dt_phieu_chi_le} AS c2 WHERE kh.ma_khach_hang = c2.ma_khach_hang AND c2.thoi_gian >= :c2_start_date AND c2.thoi_gian <= :c2_end_date)', 'chi_ngay', array(':c2_start_date' => $start_date, ':c2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(km2.so_luong * km2.tru_tien ) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS km2 ,{dt_ban_hang} AS t2 WHERE km2.ma_phieu_ban_hang = t2.ma_phieu_ban_hang and kh.ma_khach_hang = t2.ma_khach_hang AND km2.thoi_gian >= :km2_start_date AND km2.thoi_gian <= :km2_end_date)', 'km_ngay', array(':km2_start_date' => $start_date, ':km2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(h2.so_luong * h2.don_gia_tru_tien ) FROM {dt_san_pham_hoi_chi_tiet} AS h2 ,{dt_san_pham_hoi} AS t2 WHERE h2.ma_phieu_hang_hoi = t2.ma_phieu_hang_hoi and kh.ma_khach_hang = t2.ma_khach_hang AND h2.thoi_gian >= :h2_start_date AND h2.thoi_gian <= :h2_end_date)', 'hoi_ngay', array(':h2_start_date' => $start_date, ':h2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(tb2.tong_tien - tb2.tong_tien_tru - tb2.thuc_thu) FROM {dt_ban_hang} AS tb2 WHERE kh.ma_khach_hang = tb2.ma_khach_hang AND tb2.thoi_gian >= :tb2_start_date AND tb2.thoi_gian <= :tb2_end_date)', 'tbot_ngay', array(':tb2_start_date' => $start_date, ':tb2_end_date' => $end_date));
    $result = $query->orderBy('kh.ten_khach_hang')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        //l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/den_ngay/' . $rs->ma_vat_tu),
        array('data' => $rs->ten_khach_hang . '[' . $rs->ma_khach_hang . ']',),
        number_format($rs->ban_den_thang - $rs->nguyen_den_thang - $rs->thu_den_thang + $rs->du),
        $rs->ban_thang
          ? l(number_format($rs->ban_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/ban/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->thu_thang
          ? l(number_format($rs->thu_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/thu/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->nguyen_thang
          ? l(number_format($rs->nguyen_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/nguyen/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->km_thang
          ? l(number_format($rs->km_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/km/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->hoi_thang
          ? l(number_format($rs->hoi_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/hoi/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->tbot_thang
          ? l(number_format($rs->tbot_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/bot/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->chi_thang
          ? l(number_format($rs->chi_thang), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/chile/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->ban_ngay
          ? l(number_format($rs->ban_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/ban/den_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->thu_ngay
          ? l(number_format($rs->thu_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/thu/trong_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->nguyen_ngay
          ? l(number_format($rs->nguyen_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/nguyen/trong_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->km_ngay
          ? l(number_format($rs->km_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/km/trong_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->hoi_ngay
          ? l(number_format($rs->hoi_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/hoi/trong_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->tbot_ngay
          ? l(number_format($rs->tbot_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/bot/trong_ngay/' . $rs->ma_khach_hang)
          : '',
        $rs->chi_ngay
          ? l(number_format($rs->chi_ngay), 'khach_hang/danh_sach_khach_hang/chi_tiet_phieu/chile/trong_ngay/' . $rs->ma_khach_hang)
          : '',
      );
    }
    return $data;
  }

  function get_thong_ke_chi_tiet_kh($kh, $type, $start_time, $end_time) {
    switch ($type) {
      case 'ban':
        $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
        $query->condition('bh.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('bh.thoi_gian', $start_time, '>=');
        $query->condition('bh.thoi_gian', $end_time, '<=');
        $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $result = $query->limit(20)->execute();
        $data = array();
        $option_tt = get_option_ban_hang_trang_thai_ban_hang();
        $option_bht = get_option_ban_hang_trang_thai_bao_hanh_truoc();
        $header = array(
            'ma_phieu_ban_hang' => array('data' => 'Mã phiếu'),
            'ten_khach_hang' => array('data' => 'Tên khách hàng', 'field' => 'kh.ten_khach_hang'),
            'tong_tien' => array('data' => 'Tổng tiền', 'field' => 'bh.tong_tien'),
            //'thuc_thu' => array('data' => 'Thực thu', 'field' => 'bh.thuc_thu'),
            'tong_tien_tru' => array('data' => 'Tổng tiền trừ', 'field' => 'bh.tong_tien_tru'),
            'tien_bot' => array('data' => 'Tiền bớt'),
            'thuc_thu' => array('data' => 'Thực thu', 'field' => 'bh.thuc_thu'),
            //'trang_thai' => array('data' => 'Trạng thái', 'field' => 'bh.trang_thai'),
            'trang_thai_bao_hanh_truoc' => array('data' => 'Bảo hành trước', 'field' => 'bh.trang_thai_bao_hanh_truoc'),
            'ngay_thang' => array('data' => 'Ngày tháng', 'field' => 'bh.thoi_gian', 'sort' => 'desc'),
            'thao_tac' => array('data' => 'Thao tác')
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            $rs->ma_phieu_ban_hang,
            $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
            number_format($rs->tong_tien),
            number_format($rs->tong_tien_tru),
            number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
            number_format($rs->thuc_thu),
            $option_bht[$rs->trang_thai_bao_hanh_truoc],
            date('H:i:s d/m/Y', $rs->thoi_gian),
            l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
          );
        }
        break;
      case 'thu':
        $query = db_select('dt_phieu_thu_thanh_pham', 'ttp')->extend('PagerDefault')->extend('TableSort');
        $query->condition('ttp.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('ttp.thoi_gian', $start_time, '>=');
        $query->condition('ttp.thoi_gian', $end_time, '<=');
        $query->fields('ttp', array('id', 'ma_phieu_thu', 'ma_khach_hang', 'ma_thu_quy', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'nguoi_nop', 'thanh_toan', 'thoi_gian'));
        $query->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = ttp.ma_khach_hang');
        $query->addField('kd', 'ten_khach_hang');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = ttp.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $result = $query->orderBy('ttp.thoi_gian', 'DESC')->limit(15)->execute();
        $data = array();
        $header = array(
            'Mã phiếu thu',
            'Khách hàng',
            'Số tiền trước giảm',
            'Số tiền thu',
            'Tiền bớt',
            'Thủ quỹ thu',
            'Thanh toán',
            'Ngày tháng',
            'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            'ma_phieu_thu' => $rs->ma_phieu_thu,
            'ma_khach_hang' => $rs->ten_khach_hang,
            'tong_tien_chua_giam' => number_format($rs->tong_tien_chua_giam),
            'tong_tien' => number_format($rs->tong_tien),
            'tien_bot' => number_format($rs->tien_bot),
            'ma_thu_quy' => $rs->ten_thu_quy,
            'thanh_toan' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền',
            'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/thusanpham/' . $rs->ma_phieu_thu)
          );
        }
        break;
      case 'nguyen':
        $query = db_select('dt_san_pham_nguyen', 'spng')->extend('PagerDefault')->extend('TableSort');
        $query->condition('spng.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('spng.thoi_gian', $start_time, '>=');
        $query->condition('spng.thoi_gian', $end_time, '<=');
        $query->fields('spng', array('id', 'ma_phieu_hang_nguyen', 'ma_khach_hang', 'thoi_gian', 'tong_tien'));
        $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = spng.ma_khach_hang');
        $query->addField('kh', 'ten_khach_hang');
        $result = $query->orderBy('spng.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
            'Mã phiếu nhap',
            'Khách hàng',
            'Tổng tiền',
            'Ngày tháng',
            'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          if ($rs->tong_tien != 0) {
            $data[] = array(
              'ma_phieu_hang_nguyen' => $rs->ma_phieu_hang_nguyen,
              'ma_khach_hang' => $rs->ten_khach_hang,
              'tong_tien' => number_format($rs->tong_tien),
              'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
              'thao_tac' => l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/' . $type . '/' . $rs->ma_phieu_hang_nguyen)
            );
          }
        }
        break;
      case 'km':
        $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
        $query->condition('bh.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('bh.thoi_gian', $start_time, '>=');
        $query->condition('bh.thoi_gian', $end_time, '<=');
        $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $query->innerJoin('dt_ban_hang_chi_tiet_khuyen_mai', 'ctkm', 'ctkm.ma_phieu_ban_hang = bh.ma_phieu_ban_hang');
        $query->condition('ctkm.tru_tien', 0, '<>');
        $result = $query->limit(20)->execute();
        $data = array();
        $option_tt = get_option_ban_hang_trang_thai_ban_hang();
        $option_bht = get_option_ban_hang_trang_thai_bao_hanh_truoc();
        $header = array(
            'ma_phieu_ban_hang' => array('data' => 'Mã phiếu'),
            'ten_khach_hang' => array('data' => 'Tên khách hàng', 'field' => 'kh.ten_khach_hang'),
            'tong_tien' => array('data' => 'Tổng tiền', 'field' => 'bh.tong_tien'),
            //'thuc_thu' => array('data' => 'Thực thu', 'field' => 'bh.thuc_thu'),
            'tong_tien_tru' => array('data' => 'Tổng tiền trừ', 'field' => 'bh.tong_tien_tru'),
            'tien_bot' => array('data' => 'Tiền bớt'),
            'thuc_thu' => array('data' => 'Thực thu', 'field' => 'bh.thuc_thu'),
            //'trang_thai' => array('data' => 'Trạng thái', 'field' => 'bh.trang_thai'),
            'trang_thai_bao_hanh_truoc' => array('data' => 'Bảo hành trước', 'field' => 'bh.trang_thai_bao_hanh_truoc'),
            'ngay_thang' => array('data' => 'Ngày tháng', 'field' => 'bh.thoi_gian', 'sort' => 'desc'),
            'thao_tac' => array('data' => 'Thao tác')
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            $rs->ma_phieu_ban_hang,
            $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
            number_format($rs->tong_tien),
            number_format($rs->tong_tien_tru),
            number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
            number_format($rs->thuc_thu),
            $option_bht[$rs->trang_thai_bao_hanh_truoc],
            date('H:i:s d/m/Y', $rs->thoi_gian),
            l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
          );
        }
        break;
      case 'hoi':
        $query = db_select('dt_san_pham_hoi', 'sph')->extend('PagerDefault')->extend('TableSort');
        $query->condition('sph.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('sph.thoi_gian', $start_time, '>=');
        $query->condition('sph.thoi_gian', $end_time, '<=');
        $query->fields('sph', array('id', 'ma_phieu_hang_hoi', 'ma_khach_hang', 'thoi_gian', 'trang_thai'));
        $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = sph.ma_khach_hang');
        $query->addField('kh', 'ten_khach_hang');
        $query->innerJoin('dt_san_pham_hoi_chi_tiet', 'hct', 'hct.ma_phieu_hang_hoi = sph.ma_phieu_hang_hoi');
        $query->condition('hct.don_gia_tru_tien', 0, '<>');
        $result = $query->orderBy('sph.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
            'ma_phieu_ban_hang' => array('data' => 'Mã phiếu'),
            'ten_khach_hang' => array('data' => 'Tên khách hàng', 'field' => 'kh.ten_khach_hang'),
            'ngay_thang' => array('data' => 'Ngày tháng', 'field' => 'sph.thoi_gian', 'sort' => 'desc'),
            'thao_tac' => array('data' => 'Thao tác')
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            'ma_phieu_hang_hoi' => $rs->ma_phieu_hang_hoi,
            'ma_khach_hang' => $rs->ten_khach_hang,
            'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
            'thao_tac' => l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/hoi/' . $rs->ma_phieu_hang_hoi)
          );
        }
        break;
      case 'bot':
        $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
        $query->condition('bh.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('bh.thoi_gian', $start_time, '>=');
        $query->condition('bh.thoi_gian', $end_time, '<=');
        $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $result = $query->limit(20)->execute();
        $data = array();
        $header = array(
            'ma_phieu_ban_hang' => array('data' => 'Mã phiếu'),
            'ten_khach_hang' => array('data' => 'Tên khách hàng', 'field' => 'kh.ten_khach_hang'),
            'tong_tien' => array('data' => 'Tổng tiền', 'field' => 'bh.tong_tien'),
            'tong_tien_tru' => array('data' => 'Tổng tiền trừ', 'field' => 'bh.tong_tien_tru'),
            'thanh_tien' => array('data' => 'Thành tiền'),
            'thuc_thu' => array('data' => 'Thực thu', 'field' => 'bh.thuc_thu'),
            'tien_bot' => array('data' => 'Tiền bớt'),
            'ngay_thang' => array('data' => 'Ngày tháng', 'field' => 'bh.thoi_gian', 'sort' => 'desc'),
            'thao_tac' => array('data' => 'Thao tác')
        );
        while ($rs = $result->fetchObject()) {
          if (($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu) != 0) {
            $data[] = array(
              $rs->ma_phieu_ban_hang,
              $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
              number_format($rs->tong_tien),
              number_format($rs->tong_tien_tru),
              number_format($rs->tong_tien - $rs->tong_tien_tru),
              number_format($rs->thuc_thu),
              number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
              date('H:i:s d/m/Y', $rs->thoi_gian),
              l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
            );
          }
        }
        break;
      case 'chile':
        $query = db_select('dt_phieu_chi_le', 'cl')->extend('PagerDefault')->extend('TableSort');
        $query->fields('cl', array('id', 'ma_phieu_chi', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian'));
        $query->condition('cl.ma_khach_hang', $kh->ma_khach_hang, '=');
        $query->condition('cl.thoi_gian', $start_time, '>=');
        $query->condition('cl.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cl.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        // $query->orderByHeader($header);
        $result = $query->orderBy('cl.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
            'Mã phiếu chi',
            'Thủ quỹ',
            'Số tiền',
            'Người nhận',
            'Ngày tháng',
            'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu_chi, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chi),
          );
        }
        break;
    }
    return array('header' => $header, 'rows' => $data);
  }

  function update_tien_no_khach_hang($sign, $tien_no, $ma_khach_hang) {
    db_update('dt_khach_hang')
      ->expression('tien_no', 'tien_no ' . $sign . ' :tien_no', array(':tien_no' => $tien_no))
      ->condition('ma_khach_hang', $ma_khach_hang)
      ->execute();
  }

  function get_tong_khuyen_mai_theo_ma($ma_khach_hang) {
    $month = date('n');
    $year = date('Y');
    $start_month = mktime(0, 0, 0, $month, 1, $year);
    $end_month = mktime(0, 0, 0, $month + 1, 1, $year) - 1;
    $query = db_select('dt_khach_hang', 'kh');
    $query->fields('kh', array('id', 'ma_khach_hang', 'ten_khach_hang', 'dia_chi', 'so_dien_thoai', 'loai', 'tien_no', 'ghi_chu', 'thoi_gian_mua_cuoi', 'du_km_dau_thang', 'du_km'));
    $query->condition('kh.ma_khach_hang', $ma_khach_hang, '=');
    $query->addExpression('(SELECT SUM(hm.so_luong) FROM {dt_ban_hang_chi_tiet_hang_mua} AS hm INNER JOIN {dt_ban_hang} AS bh1 ON bh1.ma_phieu_ban_hang = hm.ma_phieu_ban_hang INNER JOIN {dt_san_pham} AS sp1 ON sp1.ma_thanh_pham = hm.ma_thanh_pham WHERE sp1.idloai = 1 and bh1.ma_khach_hang = :ma_kh_1 AND hm.thoi_gian >= :start_month_1 AND hm.thoi_gian <= :end_month_1)', 'tong_mua_thang', array(':ma_kh_1' => $ma_khach_hang, ':start_month_1' => $start_month, ':end_month_1' => $end_month));
    $query->addExpression('(SELECT SUM(km.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS km INNER JOIN {dt_ban_hang} AS bh2 ON bh2.ma_phieu_ban_hang = km.ma_phieu_ban_hang WHERE bh2.ma_khach_hang = :ma_kh_2 AND km.thoi_gian >= :start_month_2 AND km.thoi_gian <= :end_month_2)', 'tong_km_thang', array(':ma_kh_2' => $ma_khach_hang, ':start_month_2' => $start_month, ':end_month_2' => $end_month));
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function get_tong_thang($arr_ma, $arr_so_luong, $ma_khach_hang, $type, $loai_khach) {
    $month = date('n');
    $year = date('Y');
    $start_month = mktime(0, 0, 0, $month, 1, $year);
    $end_month = mktime(0, 0, 0, $month + 1, 1, $year) - 1;
    $query = db_select('dt_ban_hang_chi_tiet_hang_mua', 'hm');
    $query->fields('hm', array('id', 'ma_phieu_ban_hang', 'ma_thanh_pham', 'gia_tien'));
    $query->addExpression('SUM(hm.so_luong)', 'so_luong');
    $query->groupBy('hm.ma_thanh_pham');
    $query->groupBy('hm.gia_tien');
    $query->leftJoin('dt_ban_hang', 'bh', 'bh.ma_phieu_ban_hang = hm.ma_phieu_ban_hang');
    $query->leftJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = hm.ma_thanh_pham');
    $query->addField('sp', 'ten_thanh_pham');
    $query->condition('bh.ma_khach_hang', $ma_khach_hang, '=');
    $query->condition('bh.thoi_gian', $start_month, '>=');
    $query->condition('bh.thoi_gian', $end_month, '<=');
    $query->condition('sp.idloai', 1, '=');
    $result = $query->execute();
    $tong_tien = 0;
    $tong_mua_trong_thang = 0;
    $khach_hang = get_tong_khuyen_mai_theo_ma($ma_khach_hang);
    $tong_mua_trong_thang += $khach_hang->tong_mua_thang;
    foreach ($arr_ma as $key => $ma_hang) {
       $san_pham_theo_ma  =get_san_pham_theo_ma($ma_hang);
      if ($san_pham_theo_ma->idloai == 1) {
      $tong_mua_trong_thang += $arr_so_luong[$key];
      }
    }
    $moc_km = moc_khuyen_mai($tong_mua_trong_thang, $loai_khach);
    if($moc_km != null)
    {
      $moc_km_so_luong = $moc_km->so_khuyen_mai;
    }
    else
    {
      $moc_km_so_luong=0;
    }
    $tong_thang = 0;
    $data = array();
    $tru_tien = variable_get('tru_tien_khach_hang_dac_biet', 500);
    while ($rs = $result->fetchObject()) {
      $tong_thang += $rs->so_luong;
      $tong_tien += $rs->gia_tien * ($rs->so_luong * $moc_km_so_luong / 100);
      $data[] = array(
        $rs->ten_thanh_pham,
        number_format($rs->so_luong),
        number_format($moc_km_so_luong),
        $rs->so_luong * $moc_km_so_luong / 100,
        number_format($rs->gia_tien),
        number_format($rs->gia_tien * ($rs->so_luong * $moc_km_so_luong / 100))
      );
    }
    foreach ($arr_ma as $key => $ma) {
      $san_pham = get_san_pham_theo_ma($ma);
      if ($san_pham->idloai == 1) {
        $tong_thang += $arr_so_luong[$key];
        $tong_tien += $san_pham->gia_tien * ($arr_so_luong[$key] * $moc_km_so_luong / 100);
        if ($loai_khach == 1) {
          $san_pham->gia_tien = $san_pham->gia_tien - $tru_tien;
        }
        $data[] = array(
          array('data' => $san_pham->ten_thanh_pham, 'class' => array('text-bold')),
          $arr_so_luong[$key],
          number_format($moc_km_so_luong),
          $arr_so_luong[$key] * $moc_km_so_luong / 100,
          number_format($san_pham->gia_tien),
          number_format($san_pham->gia_tien * ($arr_so_luong[$key] * $moc_km_so_luong / 100)),
        );
      }
    }
    $data[] = array(
      array('data' => 'Tổng số', 'class' => array('text-bold', 'text-center')),
      number_format($tong_thang),
      array('data' => '', 'colspan' => 3),
      number_format($tong_tien),
    );
    //    $data[] = array(
    //        array('data' => 'Tổng khuyến mại', 'class' => array('text-bold','text-center')),
    //        number_format($tong_tien),
    //        array('data' => '', 'colspan' => 4),
    //    );
    if ($type == 0) {
      return $data;
    }
    else {
      return $tong_tien;
    }
  }

  function moc_khuyen_mai($so_luong, $loai_khach_hang) {
    $query = db_select('dt_khuyen_mai_thang', 'km');
    $query->fields('km', array('id'));
    //$query->condition('km.so_luong_dau', $so_luong, '<=');
    //$query->condition('km.so_luong_cuoi', $so_luong, '>');
    // $query->condition('km.trang_thai', 1, '=');
    $query->addExpression('(SELECT MAX(n1.so_luong_khuyen_mai) FROM {dt_khuyen_mai_thang} AS n1 WHERE n1.tong_so_luong_thang <= :n1_tong_so AND n1.trang_thai = 1 and n1.loai_khach_hang = :n1_loai_khach )', 'so_khuyen_mai', array(':n1_tong_so' => $so_luong, ':n1_loai_khach' => $loai_khach_hang));
    $result = $query->execute()->fetchObject();
    //    while ($rs = $result->fetchObject()) {
    //        $data = $rs->so_khuyen_mai;
    //    }
    return $result;
  }

  function get_tong_km_thang($ma_khach_hang, $type) {
    $month = date('n');
    $year = date('Y');
    $start_month = mktime(0, 0, 0, $month, 1, $year);
    $end_month = mktime(0, 0, 0, $month + 1, 1, $year) - 1;
    $query = db_select('dt_ban_hang_chi_tiet_khuyen_mai', 'km');
    $query->fields('km', array('id', 'ma_phieu_ban_hang', 'ma_thanh_pham', 'gia_tien', 'thoi_gian','so_luong'));
//    $query->addExpression('SUM(km.so_luong)', 'so_luong');
//    $query->groupBy('km.ma_thanh_pham');
//    $query->groupBy('km.gia_tien');
    $query->leftJoin('dt_ban_hang', 'bh', 'bh.ma_phieu_ban_hang = km.ma_phieu_ban_hang');
    $query->leftJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = km.ma_thanh_pham');
    $query->addField('sp', 'ten_thanh_pham');
    //    $query->leftJoin('dt_ban_hang_chi_tiet_hang_mua', 'hm', 'hm.ma_phieu_ban_hang = km.ma_phieu_ban_hang');
    //    $query->condition('hm.ma_thanh_pham', 'km.ma_thanh_pham', '=');
    //    $query->addField('hm','gia_tien');
    // $query->addField('sp','gia_tien');
    $query->condition('bh.ma_khach_hang', $ma_khach_hang, '=');
    $query->condition('bh.thoi_gian', $start_month, '>=');
    $query->condition('bh.thoi_gian', $end_month, '<=');
    $data = array();
    $tong_tien_khuyen_mai = 0;
    $tong_so = 0;
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $tong_tien_khuyen_mai += $rs->gia_tien * $rs->so_luong;
      $tong_so += $rs->so_luong;
      $data[] = array(
        $rs->ten_thanh_pham,
        $rs->so_luong,
        number_format($rs->gia_tien),
        number_format($rs->gia_tien * $rs->so_luong),
        date('d/m', $rs->thoi_gian)
      );
    }
    $data[] = array(
      array('data' => 'Tổng số', 'class' => array('text-bold', 'text-center')),
      ($tong_so),
      array('data' => ''),
      number_format($tong_tien_khuyen_mai),
      '',
    );
    if ($type == 0) {
      return $data;
    }
    else {
      return $tong_tien_khuyen_mai;
    }
  }
function xoa_khach_hang($ma_kh){
     db_update('dt_khach_hang')
      ->fields(array(
                    'hien_thi' => 1,
               ))
      ->condition('ma_khach_hang', $ma_kh)
      ->execute();
  }